package com.aaa.dao;

import com.aaa.entity.Product;
import com.aaa.entity.Ptype;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;


/**
 * 商品管理dao实现类
 */
@Repository
public class ProductDao {
    @Autowired
    private JdbcTemplate jdbcTemplate;
    private RowMapper<Product> rowMapper = new RowMapper<Product>() {
        public Product mapRow(ResultSet resultSet, int i) throws SQLException {
            Product product = new Product();
            product.setPid(resultSet.getLong("pid"));
            product.setPname(resultSet.getString("pname"));
            product.setPrice(resultSet.getDouble("price"));
            product.setImgPath(resultSet.getString("img_path"));
            product.setCreate_date(resultSet.getString("create_date"));
            Ptype ptype = new Ptype();
            ptype.setTid(resultSet.getLong("tid"));
            ptype.setTname(resultSet.getString("tname"));
            product.setPtype(ptype);
            return product;

        }
    };

    private RowMapper<Ptype> rowMapper1=new RowMapper<Ptype>() {
        public Ptype mapRow(ResultSet resultSet, int i) throws SQLException {
            Ptype ptype = new Ptype();
            ptype.setTid(resultSet.getLong("tid"));
            ptype.setTname(resultSet.getString("tname"));
            return ptype;
        }
    };

    /**
     * 查询当前页数据
     * @param offset
     * @param limit
     * @return
     */
    public List<Product> listPage(String keyword, Integer offset, Integer limit){
        String sql = "select p.pid,p.pname,p.price,p.tid,p.img_path,p.create_date,t.tname from product p inner join ptype t on p.tid=t.tid where pname like ? order by pid  desc limit ?,? ";
        List<Product> result = this.jdbcTemplate.query(sql, rowMapper,"%"+keyword+"%", offset, limit);
        return  result;
    }

    /**
     * 查询总条数
     * @return
     */
    public Integer count(String keyword){
        String sql = "select count(pid) as c from product where pname like ? ";
        Integer result = this.jdbcTemplate.queryForObject(sql, new RowMapper<Integer>() {
            public Integer mapRow(ResultSet resultSet, int i) throws SQLException {
                return resultSet.getInt("c");
            }
        },"%"+keyword+"%");
        return  result;
    }


    /**
     * 保存商品信息
     * @param product
     * @return
     */
    public Integer save(Product product){
        String sql = "insert into product (pname,price,img_path,create_date,tid) values(?,?,?,?)";
        int count = this.jdbcTemplate.update(sql, product.getPname(), product.getPrice(),product.getImgPath(),product.getCreate_date(),product.getPtype().getTid());
        return  count;
    }

    /**
     * 修改商品信息
     * @param product
     * @return
     */
    public Integer update(Product product){
        String sql = "update product set pname=?,price=?,img_path=?,tid=? where pid=?";
        int count = this.jdbcTemplate.update(sql, product.getPname(), product.getPrice(),product.getImgPath(),product.getPtype().getTid(),product.getPid());
        return count;
    }

    /**
     * 删除商品信息
     * @param pid
     * @return
     */
    public Integer delete(Long pid){
        String sql = "delete from product where pid =?";
        int count = this.jdbcTemplate.update(sql,pid);
        return count;
    }
    public List<Ptype> fuPtypeList(){
        String sql="select tid,tname from ptype";
        List<Ptype> query = this.jdbcTemplate.query(sql, rowMapper1);
        return query;

    }

}
